package com.hospital.dao;

import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.hospital.util.*;
import com.hospital.model.UserInfo;

import jxl.Workbook;
import jxl.read.biff.BiffException;

public class Data {

	public void getDate(String filename){
		List liststu=new ArrayList();
		// 找到导入的文件
		//InputStream is= Date.class.getClassLoader().getResourceAsStream(filename);
		try {
			//创建工作簿
			Workbook wb=Workbook.getWorkbook(new File("D:\\Tomcat\\apache-tomcat-7.0.50\\webapps\\hospital\\upload\\"+filename));
			//Workbook wb=Workbook.getWorkbook(is);
			//创建工作表
			jxl.Sheet sheet=wb.getSheet(0);
			String content=null; 
			for(int i=1;i<sheet.getRows();i++) {
				UserInfo user=new UserInfo();
				for(int j=0;j<sheet.getColumns();j++) {
					content=sheet.getCell(j, i).getContents();
					if(user.getAccount()==null) {
						user.setAccount(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getPswd()==null) {
						user.setPswd(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getRoot()==null) {
						user.setRoot(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getSex()==null) {
						user.setSex(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getBirthday()==null) {
						user.setBirthday(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getAddress()==null) {
						user.setAddress(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getTel()==null) {
						user.setTel(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getMoney()==null) {
						user.setMoney(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getDepartmentName()==null) {
						user.setDepartmentName(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getSchool()==null) {
						user.setSchool(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getEducation()==null) {
						user.setEducation(sheet.getCell(j, i).getContents());
						continue;
					}
					if(user.getDeptId()==null) {
						user.setDeptId(sheet.getCell(j, i).getContents());
						continue;
					}
				}
				getUserInfo(user);
			}

		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();


		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void getUserInfo(UserInfo user){
		if(user.getAccount()!=null){
			String sql = "insert into t_user_info(deptId , account, pswd, root, sex, birthday, address, tel, departmentName, money, school, education) values('"
					+ user.getDeptId() +"', '" + user.getAccount() + "', '" + MD5.MD5(user.getPswd()) + "','" + user.getRoot() + "', '" + user.getSex() + "', '" + user.getBirthday() + "', '" + user.getAddress() + "', '" + user.getTel() + "', '" + user.getDepartmentName() +"', '" + 
					user.getMoney() +"', '" +user.getSchool()+"', '" +user.getEducation()+ "')";
			DbUtil.add_modify(sql);
		}
	}

}
